﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Reflection;
using System.Collections.Specialized;
using System.Data;

/*
 * Normal stands for
 * .Net Object Relational Mapping Application Library
 */
namespace Normal
{
    public enum DatabaseType { MsSQL, Oracle, Firebird, MySQL }

    /// <summary>
    /// Allows to specify a Generic Data Abstraction Layer to the underlying database
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DAO<T> where T : class
    {
        protected DbConnection _connection;
        protected DbProviderFactory _factory;
        protected DatabaseType _databaseType;
        protected string _providerName;
        protected string _connectionString;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="ConnectionString">Specifies the Connection string to underlying database</param>
        /// <param name="ProviderName">Provider name</param>
        public DAO(string ConnectionString, string ProviderName)
        {
            _providerName = ProviderName;
            _connectionString = ConnectionString;
            try
            {
                _factory = DbProviderFactories.GetFactory(_providerName);
            }
            catch
            {
                throw new ArgumentException("The provider " + _providerName + " is not installed");
            }
            _connection = _factory.CreateConnection();
            _connection.ConnectionString = ConnectionString;
            if (_providerName.Equals("System.Data.SqlClient"))
            {
                _databaseType = DatabaseType.MsSQL;
            }
            if (_providerName.Equals("System.Data.OracleClient"))
            {
                _databaseType = DatabaseType.Oracle;
            }
            if (_providerName.Equals("MySql.Data.MySqlClient"))
            {
                _databaseType = DatabaseType.MySQL;
            }
            if (_providerName.Equals("FirebirdSql.Data.FirebirdClient"))
            {
                _databaseType = DatabaseType.Firebird;
            }
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connection">Specifies an existing connection to the underlying database</param>
        /// <param name="ProviderName">Provider name</param>
        public DAO(DbConnection connection, string ProviderName)
        {
            _providerName = ProviderName;
            _connection = connection;
            _connectionString = connection.ConnectionString;
            if (_providerName.Equals("System.Data.SqlClient"))
            {
                _databaseType = DatabaseType.MsSQL;
            }
            if (_providerName.Equals("System.Data.OracleClient"))
            {
                _databaseType = DatabaseType.Oracle;
            }
            if (_providerName.Equals("MySql.Data.MySqlClient"))
            {
                _databaseType = DatabaseType.MySQL;
            }
            if (_providerName.Equals("FirebirdSql.Data.FirebirdClient"))
            {
                _databaseType = DatabaseType.Firebird;
            }
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="ConnectionString">Specifies the Connection string to underlying database</param>
        /// <param name="databaseType">Specifies the Database Type</param>
        public DAO(string ConnectionString, DatabaseType databaseType)
        {
            _databaseType = databaseType;
            _connectionString = ConnectionString;
            switch (databaseType)
            {
                case DatabaseType.MsSQL:
                    _providerName = "System.Data.SqlClient";
                    break;
                case DatabaseType.Oracle:
                    _providerName = "System.Data.OracleClient";
                    break;
                case DatabaseType.MySQL:
                    _providerName = "MySql.Data.MySqlClient";
                    break;
                case DatabaseType.Firebird:
                    _providerName = "FirebirdSql.Data.FirebirdClient";
                    break;
            }
            try
            {
                _factory = DbProviderFactories.GetFactory(_providerName);
            }
            catch
            {
                throw new ArgumentException("The provider " + _providerName + " is not installed");
            }
            _connection = _factory.CreateConnection();
            _connection.ConnectionString = ConnectionString;
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connection">Specifies an existing connection to the underlying database</param>
        /// <param name="databaseType">Specifies the Database Type</param>
        public DAO(DbConnection connection, DatabaseType databaseType)
        {
            _databaseType = databaseType;
            _connection = connection;
            _connectionString = connection.ConnectionString;
            switch (databaseType)
            {
                case DatabaseType.MsSQL:
                    _providerName = "System.Data.SqlClient";
                    break;
                case DatabaseType.Oracle:
                    _providerName = "System.Data.OracleClient";
                    break;
                case DatabaseType.MySQL:
                    _providerName = "MySql.Data.MySqlClient";
                    break;
                case DatabaseType.Firebird:
                    _providerName = "FirebirdSql.Data.FirebirdClient";
                    break;
            }
        }

        /// <summary>
        /// Insert a object in to the underlying database
        /// </summary>
        /// <param name="ObjectToBeInserted">The serializable to be inserted</param>
        public void Insert(T ObjectToBeInserted)
        {
            Insert(ObjectToBeInserted, null);
        }

        /// <summary>
        /// Insert a object in to the underlying database
        /// </summary>
        /// <param name="ObjectToBeInserted">The serializable to be inserted</param>
        /// <param name="trn">The existing transaction</param>
        public void Insert(T ObjectToBeInserted, DbTransaction trn)
        {
            StringBuilder sbCmd = new StringBuilder();
            DbCommand dbCmd = _factory.CreateCommand();
            string _autoNumberQuery = GetAutoNumberQuery();
            bool ControlConnection = false;

            if (trn != null)
            {
                dbCmd.Connection = trn.Connection;
                _connection = dbCmd.Connection;
                dbCmd.Transaction = trn;
            }
            else
            {
                if (_connection.State != ConnectionState.Open)
                {
                    Connect(_connection);
                    ControlConnection = true;
                }
                dbCmd.Connection = _connection;
            }

            CheckSequence(_connection, trn);
            dbCmd.CommandType = CommandType.Text;
            try
            {
                AutoNumber _anumber = GetAutoNumberAttribute();
                if (_anumber.Strategy == AutoNumberStrategy.Increment)
                {
                    string strIncrement = "Select ";
                    foreach (PropertyInfo prop in typeof(T).GetProperties())
                    {
                        foreach (object idAttr in prop.GetCustomAttributes(typeof(Id), true))
                        {
                            strIncrement += "Max(" + ((Id)idAttr).Name + "), ";
                        }
                    }
                    strIncrement = strIncrement.Substring(0, strIncrement.Length - 2);
                    strIncrement += " From " + GetTableName(typeof(T));
                    DbCommand cmdIncr = _factory.CreateCommand();
                    cmdIncr.Connection = _connection;
                    cmdIncr.Transaction = trn;
                    cmdIncr.CommandText = strIncrement;
                    cmdIncr.CommandType = CommandType.Text;
                    DbDataReader readerIncr = cmdIncr.ExecuteReader();
                    while (readerIncr.Read())
                    {
                        int x = 0;
                        foreach (PropertyInfo prop in typeof(T).GetProperties())
                        {
                            foreach (object idAttr in prop.GetCustomAttributes(typeof(Id), true))
                            {
                                long key = 0;
                                key = Convert.ToInt32(readerIncr[x]) + 1;
                                if (prop.PropertyType.ToString() == typeof(Int16).ToString())
                                {
                                    ObjectToBeInserted.GetType().InvokeMember(prop.Name,
                                        BindingFlags.SetProperty, null,
                                        ObjectToBeInserted,
                                        new object[] { Convert.ToInt16(key) });
                                }
                                if (prop.PropertyType.ToString() == typeof(Int32).ToString())
                                {
                                    ObjectToBeInserted.GetType().InvokeMember(prop.Name,
                                        BindingFlags.SetProperty, null,
                                        ObjectToBeInserted,
                                        new object[] { Convert.ToInt32(key) });
                                }
                                if (prop.PropertyType.ToString() == typeof(Int64).ToString())
                                {
                                    ObjectToBeInserted.GetType().InvokeMember(prop.Name,
                                        BindingFlags.SetProperty, null,
                                        ObjectToBeInserted,
                                        new object[] { Convert.ToInt64(key) });
                                }
                            }
                        }
                    }
                    readerIncr.Close();
                    readerIncr = null;
                    cmdIncr = null;
                }
                sbCmd.AppendLine("Insert Into " + GetTableName(typeof(T)));
                sbCmd.AppendLine("(");
                GetSql(typeof(T), sbCmd, (_anumber.Strategy == AutoNumberStrategy.Native));
                sbCmd.AppendLine(")");
                sbCmd.AppendLine("Values");
                sbCmd.AppendLine("(");
                GetSqlInsert(typeof(T), sbCmd);
                sbCmd.AppendLine(")");

                dbCmd.CommandText = sbCmd.ToString();
                FillParameters(dbCmd, ObjectToBeInserted, (_anumber.Strategy == AutoNumberStrategy.Native));
                if (_anumber != null && _anumber.Strategy == AutoNumberStrategy.Sequence)
                {
                    foreach (PropertyInfo prop in typeof(T).GetProperties())
                    {
                        foreach (object idAttr in prop.GetCustomAttributes(typeof(Id), true))
                        {
                            string UpdateSequence = "Update " + _anumber.SequenceTableName +
                                " Set " + _anumber.SequenceId + " = " + _anumber.SequenceId + " + 1" +
                                " Where " + _anumber.SequenceDescription + " = '" + GetTableName(typeof(T)) + "'";
                            string SQLSequence = "Select " + _anumber.SequenceId +
                                " From " + _anumber.SequenceTableName +
                                " Where " + _anumber.SequenceDescription + " = '" + GetTableName(typeof(T)) + "'";
                            DbCommand seqCmd = _connection.CreateCommand();
                            seqCmd.Transaction = trn;
                            seqCmd.CommandType = CommandType.Text;
                            seqCmd.CommandText = UpdateSequence;
                            seqCmd.ExecuteNonQuery();
                            seqCmd = _connection.CreateCommand();
                            seqCmd.CommandType = CommandType.Text;
                            seqCmd.Transaction = trn;
                            seqCmd.CommandText = SQLSequence;
                            DbDataReader seqReader = seqCmd.ExecuteReader();
                            if (seqReader.Read())
                            {
                                dbCmd.Parameters[GetParameterPrefix() + ((Id)idAttr).Name].Value = seqReader[0];
                            }
                            seqReader.Close();
                            seqReader = null;
                        }
                    }
                }
                dbCmd.ExecuteNonQuery();

                if (Convert.ToString(_autoNumberQuery) != "")
                {
                    dbCmd.Parameters.Clear();
                    dbCmd.CommandText = _autoNumberQuery;
                    dbCmd.Transaction = trn;
                    foreach (PropertyInfo prop in typeof(T).GetProperties())
                    {
                        foreach (object attrId in prop.GetCustomAttributes(typeof(Id), true))
                        {
                            if (prop.PropertyType.ToString() == typeof(Int16).ToString())
                            {
                                ObjectToBeInserted.GetType().InvokeMember(prop.Name, BindingFlags.SetProperty, null, ObjectToBeInserted, new object[] { Convert.ToInt16(dbCmd.ExecuteScalar()) });
                                break;
                            }
                            if (prop.PropertyType.ToString() == typeof(Int32).ToString())
                            {
                                ObjectToBeInserted.GetType().InvokeMember(prop.Name, BindingFlags.SetProperty, null, ObjectToBeInserted, new object[] { Convert.ToInt32(dbCmd.ExecuteScalar()) });
                                break;
                            }
                            if (prop.PropertyType.ToString() == typeof(Int64).ToString())
                            {
                                ObjectToBeInserted.GetType().InvokeMember(prop.Name, BindingFlags.SetProperty, null, ObjectToBeInserted, new object[] { Convert.ToInt64(dbCmd.ExecuteScalar()) });
                                break;
                            }
                        }
                    }
                }
            }
            finally
            {
                if (_connection.State == ConnectionState.Open && ControlConnection)
                {
                    Disconnect(_connection);
                }
            }
        }

        /// <summary>
        /// Update a object in to the underlying database
        /// </summary>
        /// <param name="ObjectToBeUpdated">The serializable to be updated</param>
        public void Update(T ObjectToBeUpdated)
        {
            Update(ObjectToBeUpdated, null);
        }

        /// <summary>
        /// Update a object in to the underlying database
        /// </summary>
        /// <param name="ObjectToBeUpdated">The serializable to be updated</param>
        /// <param name="trn">The existing transaction</param>
        public void Update(T ObjectToBeUpdated, DbTransaction trn)
        {
            StringBuilder sbCmd = new StringBuilder();
            DbCommand dbCmd = _connection.CreateCommand();
            bool ControlConnection = false;
            if (trn != null)
            {
                dbCmd.Connection = trn.Connection;
                _connection = dbCmd.Connection;
                dbCmd.Transaction = trn;
            }
            else
            {
                if (_connection.State != ConnectionState.Open)
                {
                    Connect(_connection);
                    ControlConnection = true;
                }
                dbCmd.Connection = _connection;
            }
            dbCmd.CommandType = CommandType.Text;
            try
            {

                sbCmd.AppendLine("Update " + GetTableName(typeof(T)) + " Set ");
                GetSqlUpdate(typeof(T), sbCmd);
                sbCmd.AppendLine("");
                GetWherePk(dbCmd, sbCmd, GetPk(ObjectToBeUpdated), typeof(T));
                dbCmd.Parameters.Clear();

                dbCmd.CommandText = sbCmd.ToString();
                FillParameters(dbCmd, ObjectToBeUpdated, false);

                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                if (_connection.State == ConnectionState.Open && ControlConnection)
                {
                    Disconnect(_connection);
                }
            }
        }

        /// <summary>
        /// Checks the sequence table and create if doesn't exists
        /// </summary>
        /// <param name="cnn">Existing Connection</param>
        /// <param name="trn">Existing Transaction Context, if any</param>
        protected void CheckSequence(DbConnection cnn, DbTransaction trn)
        {
            AutoNumber _anumber = GetAutoNumberAttribute();
            if (_anumber == null || _anumber.Strategy != AutoNumberStrategy.Sequence)
            {
                return;
            }
            string SQLSequence = "Select " + _anumber.SequenceId +
                " From " + _anumber.SequenceTableName +
                " Where " + _anumber.SequenceDescription + " = '" + GetTableName(typeof(T)) + "'";
            DbCommand cmd = cnn.CreateCommand();
            cmd.Transaction = trn;
            cmd.CommandText = SQLSequence;
            cmd.CommandType = CommandType.Text;
            DbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
            }
            catch
            {
                cmd.CommandText = "Create Table " + _anumber.SequenceTableName +
                    "(" + _anumber.SequenceDescription + " varchar(50), " +
                    _anumber.SequenceId + " int )";
                cmd.ExecuteNonQuery();
                cmd.CommandText = SQLSequence;
                reader = cmd.ExecuteReader();
            }
            if (!reader.Read())
            {
                reader.Close();
                reader = null;
                cmd = cnn.CreateCommand();
                cmd.Transaction = trn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Insert Into " + _anumber.SequenceTableName +
                    "(" + _anumber.SequenceDescription + "," + _anumber.SequenceId +
                    ") Values('" + GetTableName(typeof(T)) + "',0)";
                cmd.ExecuteNonQuery();
            }
            if (reader != null)
            {
                reader.Close();
                reader = null;
            }
            cmd = null;
        }

        /// <summary>
        /// Removes a object from the underlying database
        /// </summary>
        /// <param name="id">The id of the object to be deleted</param>
        public void Delete(object[] id)
        {
            Delete(id, null);
        }

        /// <summary>
        /// Removes a object from the underlying database
        /// </summary>
        /// <param name="id">The id of the object to be deleted</param>
        /// <param name="trn">The existing transaction</param>
        public void Delete(object[] id, DbTransaction trn)
        {
            DbConnection cnn = _connection;
            bool ControlConnection = false;
            if (trn != null)
            {
                cnn = trn.Connection;
            }
            else
            {
                Connect(cnn);
                ControlConnection = true;
            }
            try
            {
                StringBuilder sbDelete = new StringBuilder();
                DbCommand cmdDelete = cnn.CreateCommand();
                cmdDelete.Connection = cnn;
                cmdDelete.Transaction = trn;
                cmdDelete.CommandType = CommandType.Text;
                foreach (object _tblAttr in typeof(T).GetCustomAttributes(typeof(Table), true))
                {
                    sbDelete.AppendLine("Delete From " + GetTableName(typeof(T)));
                    break;
                }
                GetWherePk(cmdDelete, sbDelete, id, typeof(T));
                cmdDelete.CommandText = sbDelete.ToString();
                cmdDelete.ExecuteNonQuery();
            }
            finally
            {
                if (cnn.State == ConnectionState.Open && ControlConnection)
                {
                    Disconnect(cnn);
                }
            }
        }

        /// <summary>
        /// Removes a object from the underlying database
        /// </summary>
        /// <param name="id">The id of the object to be deleted</param>
        public void Delete(object id)
        {
            Delete(new object[] { id }, null);
        }

        /// <summary>
        /// Removes a object from the underlying database
        /// </summary>
        /// <param name="id">The id of the object to be deleted</param>
        /// <param name="trn">The transaction context</param>
        public void Delete(object id, DbTransaction trn)
        {
            Delete(new object[] { id }, trn);
        }


        /// <summary>
        /// Retrieves an existing object from underlying database
        /// </summary>
        /// <param name="id">The object id from the object</param>
        /// <returns>An object array representing the item from database</returns>
        public T FindById(object id)
        {
            return FindById(new object[] { id });
        }

        /// <summary>
        /// Retrieves an existing object from underlying database
        /// </summary>
        /// <param name="id">The object id from the object</param>
        /// <returns>An object representing the item from database</returns>
        public T FindById(object[] id)
        {
            return FindById(id, typeof(T), null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="tipe"></param>
        /// <returns></returns>
        internal T FindById(object[] id, Type tipe, DbTransaction trn)
        {
            try
            {
                StringBuilder sbSql = new StringBuilder();
                sbSql.AppendLine("Select ");
                GetSql(tipe, sbSql, false);
                sbSql.AppendLine();
                sbSql.AppendLine(" From " + GetTableName(tipe));

                DbCommand cmdFind = _connection.CreateCommand();
                cmdFind.Transaction = trn;
                if (_connection.State != ConnectionState.Open)
                {
                    Connect(_connection);
                }
                cmdFind.CommandType = CommandType.Text;
                GetWherePk(cmdFind, sbSql, id, tipe);
                cmdFind.CommandText = sbSql.ToString();
                DbDataReader reader = cmdFind.ExecuteReader();
                if (!reader.Read())
                {
                    return null;
                }

                T ReturnObject = (T)NormalProxy.CreateProxy(Activator.CreateInstance(tipe),
                    _connectionString, _providerName);
                FillObject(ReturnObject, reader, tipe);
                return ReturnObject;
            }
            finally
            {
                if (_connection.State != ConnectionState.Closed)
                {
                    Disconnect(_connection);
                }
            }
        }

        /// <summary>
        /// Retrieves existing objects from underlying database.
        /// Be careful with tables with huge number of records.
        /// </summary>
        /// <returns>A list of objects representing all rows from the specified table</returns>
        public List<T> FindAll()
        {
            long NumberOfPages = 0;
            return FindAll(0, 0, out NumberOfPages);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database.
        /// Be careful with tables with huge number of records.
        /// </summary>
        /// <param name="SortCriteria">Defines the list of properties to be used by sorting</param>
        /// <returns></returns>
        public List<T> FindAll(List<string> SortCriteria)
        {
            long NumberOfPages = 0;
            return FindAll(0, 0, out NumberOfPages);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database.
        /// </summary>
        /// <param name="PageNumber">Number of the Page</param>
        /// <param name="PageSize">Page Size</param>
        /// <param name="NumberOfPages">Total of the Pages</param>
        /// <returns></returns>
        public List<T> FindAll(long PageNumber, long PageSize, out long NumberOfPages)
        {
            return FindAll(PageNumber, PageSize, out NumberOfPages, null);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database.
        /// </summary>
        /// <param name="PageNumber">Number of the Page</param>
        /// <param name="PageSize">Page Size</param>
        /// <param name="NumberOfPages">Total of the Pages</param>
        /// <param name="SortCriteria">Defines the list of properties to be used by sorting</param>
        /// <returns></returns>
        public List<T> FindAll(long PageNumber, long PageSize, out long NumberOfPages, List<string> SortCriteria)
        {
            return FindByCriteria(null, PageNumber, PageSize, out NumberOfPages, SortCriteria);
        }

        /// <summary>
        /// Provides the Limit Keyword that applies to Select clause
        /// </summary>
        /// <returns></returns>
        protected string GetLimitSelectKeyWord(long RecordNumber)
        {
            string _return = "";
            switch (_databaseType)
            {
                case DatabaseType.MsSQL:
                    _return = " Top " + RecordNumber;
                    break;
                case DatabaseType.Oracle:
                    _return = "";
                    break;
                case DatabaseType.MySQL:
                    _return = "";
                    break;
                case DatabaseType.Firebird:
                    _return = " First " + RecordNumber;
                    break;
            }
            return _return;
        }

        /// <summary>
        /// Provides the limit Keyword to be applied in Where clause
        /// </summary>
        /// <returns></returns>
        protected string GetLimitWhereKeyWord(long RecordNumber)
        {
            string _return = "";
            switch (_databaseType)
            {
                case DatabaseType.MsSQL:
                    _return = "";
                    break;
                case DatabaseType.Oracle:
                    _return = " Where RowNumber <= " + RecordNumber;
                    break;
                case DatabaseType.MySQL:
                    _return = " Limit " + RecordNumber;
                    break;
                case DatabaseType.Firebird:
                    _return = "";
                    break;
            }
            return _return;
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on search criteria
        /// </summary>
        /// <param name="Criteria">List of strings with criteria to the underlying database. The criteria supports property names well as databas colum names</param>
        /// <returns>A list of objects representing the rows from the specified table that fits with Criteria</returns>
        public List<T> FindByCriteria(List<string> Criteria)
        {
            long NumberOfPages = 0;
            return FindByCriteria(Criteria, 0, 0, out NumberOfPages);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on search criteria
        /// </summary>
        /// <param name="Criteria">List of strings with criteria to the underlying database. The criteria supports property names well as databas colum names</param>
        /// <param name="SortCriteria">Defines the list of properties to be used by sorting</param>
        /// <returns>A list of objects representing the rows from the specified table that fits with Criteria</returns>
        public List<T> FindByCriteria(List<string> Criteria, List<string> SortCriteria)
        {
            long NumberOfPages = 0;
            return FindByCriteria(Criteria, 0, 0, out NumberOfPages, SortCriteria);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on search criteria
        /// </summary>
        /// <param name="Criteria">List of strings with criteria to the underlying database. The criteria supports property names well as databas colum names</param>
        /// <param name="PageNumber">Number of the Pages</param>
        /// <param name="PageSize">Page Size</param>
        /// <param name="NumberOfPages"></param>
        /// <returns>A list of objects representing the rows from the specified table that fits with Criteria</returns>
        public List<T> FindByCriteria(List<string> Criteria, long PageNumber, long PageSize, out long NumberOfPages)
        {
            return FindByCriteria(Criteria, PageNumber, PageSize, out NumberOfPages, null);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on search criteria
        /// </summary>
        /// <param name="Criteria">List of strings with criteria to the underlying database. The criteria supports property names well as databas colum names</param>
        /// <param name="PageNumber">Number of the Pages</param>
        /// <param name="PageSize">Page Size</param>
        /// <param name="NumberOfPages"></param>
        /// <param name="SortCriteria">Defines the list of properties to be used by sorting</param>
        /// <returns>A list of objects representing the rows from the specified table that fits with Criteria</returns>
        public List<T> FindByCriteria(List<string> Criteria, long PageNumber, long PageSize, out long NumberOfPages, List<string> SortCriteria)
        {
            try
            {
                NumberOfPages = NumberOfRecords(Criteria);
                if (PageSize != 0)
                {
                    NumberOfPages = NumberOfPages / PageSize + (NumberOfPages % PageSize != 0 ? 1 : 0);
                }
                StringBuilder sbSql = new StringBuilder();
                sbSql.AppendLine("Select");
                if (PageNumber > 0 && PageSize > 0)
                {
                    sbSql.AppendLine(GetLimitSelectKeyWord(PageNumber * PageSize));
                }
                GetSql(typeof(T), sbSql, false);
                sbSql.AppendLine();
                foreach (object _tblAttr in typeof(T).GetCustomAttributes(typeof(Table), true))
                {
                    sbSql.AppendLine("From " + ((Table)_tblAttr).TableName);
                }
                if (Criteria != null)
                {
                    if (Criteria.Count > 0)
                    {
                        if (sbSql.ToString().Contains("Where "))
                        {
                            sbSql.AppendLine("And " + ReplaceBySqlDefinition(typeof(T), Criteria[0]));
                        }
                        else
                        {
                            sbSql.AppendLine("Where " + ReplaceBySqlDefinition(typeof(T), Criteria[0]));
                        }
                        for (int x = 1; x < Criteria.Count; x++)
                        {
                            sbSql.AppendLine("And " + ReplaceBySqlDefinition(typeof(T), Criteria[x]));
                        }
                    }
                }
                if (SortCriteria != null)
                {
                    sbSql.AppendLine("Order By " + ReplaceBySqlDefinition(typeof(T), string.Join(",", SortCriteria.ToArray())));
                }
                if (PageNumber > 0 && PageSize > 0)
                {
                    sbSql.AppendLine(GetLimitWhereKeyWord(PageNumber * PageSize));
                }
                DbCommand cmdFind = _connection.CreateCommand();
                if (_connection.State != ConnectionState.Open)
                {
                    Connect(_connection);
                }
                cmdFind.CommandType = CommandType.Text;
                cmdFind.CommandText = sbSql.ToString();
                DbDataReader reader = cmdFind.ExecuteReader();
                List<T> ReturnList = new List<T>();
                for (long x = 1; x <= ((PageNumber - 1) * PageSize); x++)
                {
                    if (!reader.Read())
                    {
                        break;
                    }
                }
                while (reader.Read())
                {
                    T ReturnObject = (T)NormalProxy.CreateProxy(Activator.CreateInstance(typeof(T)),
                        _connectionString, _providerName);
                    FillObject(ReturnObject, reader, typeof(T));
                    ReturnList.Add(ReturnObject);
                }
                return ReturnList;
            }
            finally
            {
                if (_connection.State != ConnectionState.Closed)
                {
                    Disconnect(_connection);
                }
            }
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on a given object
        /// </summary>
        /// <param name="Example">The example of the object where the non-empty properties will be used to search</param>
        /// <returns>A list of objects representing the rows from the specified table that fits with Criteria</returns>
        public List<T> FindByExample(T Example)
        {
            long NumberOfPages = 0;
            return FindByExample(Example, 0, 0, out NumberOfPages);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on a given object
        /// </summary>
        /// <param name="Example">The example of the object where the non-empty properties will be used to search</param>
        /// <param name="PageNumber">Page Number</param>
        /// <param name="PageSize">Page Size</param>
        /// <param name="NumberOfPages">Total Number of Pages</param>
        /// <returns>A list of objects representing the rows from the specified table that fits with Criteria</returns>
        public List<T> FindByExample(T Example, long PageNumber, long PageSize, out long NumberOfPages)
        {
            List<string> Criteria = CreateCriteria(Example);
            return FindByCriteria(Criteria, PageNumber, PageSize, out NumberOfPages);
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on a given object
        /// </summary>
        /// <param name="Clazz">The object type that will be inferred</param>
        /// <param name="sbSql">The existing StringBuilder that will be added by fields</param>
        /// <param name="DoNotIncludeId">If the field list will contain Id fields</param>
        protected void GetSql(Type Clazz, StringBuilder sbSql, bool DoNotIncludeId)
        {
            bool passou = false;
            AutoNumber _anumber = GetAutoNumberAttribute();
            foreach (PropertyInfo Info in Clazz.GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    if (!DoNotIncludeId)
                    {
                        sbSql.AppendLine("\t" + ((Id)attr).Name + ", ");
                        passou = true;
                    }
                }
                foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                {
                    sbSql.AppendLine("\t" + ((Column)attr).Name + ", ");
                    passou = true;
                }
            }
            if (passou)
            {
                sbSql = sbSql.Remove(sbSql.Length - 4, 4);
            }
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on a given object
        /// </summary>
        /// <param name="Clazz">The object type that will be inferred</param>
        /// <param name="FieldExpression">The existing Expression that will be replaced by Column definition</param>
        /// <returns>The string replaced by Column Definition</returns>
        protected string ReplaceBySqlDefinition(Type Clazz, string FieldExpression)
        {
            foreach (object attr in Clazz.GetCustomAttributes(true))
            {
                if (attr is Table)
                {
                    FieldExpression = FieldExpression.Replace(Clazz.Name, ((Table)attr).TableName);
                }
            }
            foreach (PropertyInfo Info in Clazz.GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    FieldExpression = FieldExpression.Replace(Info.Name, ((Id)attr).Name);
                }
                foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                {
                    FieldExpression = FieldExpression.Replace(Info.Name, ((Column)attr).Name);
                }
            }
            return FieldExpression;
        }

        /// <summary>
        /// Fills the StringBuilder with Primary Key Information
        /// </summary>
        /// <param name="cmdPk">Command where the parameters will created or filled</param>
        /// <param name="sbPk">StringBuilder to be filled</param>
        /// <param name="id">The Primary Key Id</param>
        protected void GetWherePk(DbCommand cmdPk, StringBuilder sbPk, object id, Type Clazz)
        {
            GetWherePk(cmdPk, sbPk, new object[] { id }, Clazz);
        }

        /// <summary>
        /// Fills the StringBuilder with Primary Key Information
        /// </summary>
        /// <param name="cmdPk">Command where the parameters will created or filled</param>
        /// <param name="sbPk">StringBuilder to be filled</param>
        /// <param name="id">The Primary Key Id</param>
        protected void GetWherePk(DbCommand cmdPk, StringBuilder sbPk, object[] id, Type Clazz)
        {
            int x = 0;
            foreach (PropertyInfo Info in Clazz.GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    if (x < id.GetLength(0))
                    {
                        sbPk.AppendLine((x == 0 ? " Where " : " And ") + ((Id)attr).Name + " = " + GetParameterPrefix() + ((Id)attr).Name);
                        DbParameter _par = _factory.CreateParameter();
                        _par.ParameterName = GetParameterPrefix() + ((Id)attr).Name;
                        _par.Value = id[x];
                        cmdPk.Parameters.Add(_par);
                        x++;
                    }
                    else
                    {
                        throw new IndexOutOfRangeException("Insufficient number of parameters for the search");
                    }
                }
            }
        }

        /// <summary>
        /// Retrieves existing objects from underlying database, based on a given object
        /// </summary>
        /// <param name="ReturnObject">The object instance where the properties would be filled</param>
        /// <param name="Reader">The DbDataReader where the column collection will be used to fill</param>
        protected bool FillObject(T ReturnObject, DbDataReader Reader, Type tipe)
        {
            bool Filled = false;
            foreach (PropertyInfo Info in tipe.GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    if (ContainsColumn(Reader, ((Id)attr).Name.ToString()))
                    {
                        if (!Reader[((Id)attr).Name.ToString()].Equals(System.DBNull.Value))
                        {
                            if (Info.PropertyType.GetCustomAttributes(typeof(Table), true).Length > 0)
                            {
                                object fk = NormalFKProxy.CreateProxy(Activator.CreateInstance(Info.PropertyType),
                                    Reader[((Id)attr).Name.ToString()], _connectionString, _providerName);
                                tipe.InvokeMember(Info.Name, BindingFlags.SetProperty, null,
                                ReturnObject, new object[] { fk });
                                Filled = true;
                            }
                            else
                            {
                                tipe.InvokeMember(Info.Name, BindingFlags.SetProperty, null,
                                    ReturnObject, new object[] { Reader[((Id)attr).Name.ToString()] });
                                Filled = true;
                            }
                        }
                    }
                }
                foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                {
                    if (ContainsColumn(Reader, ((Column)attr).Name.ToString()))
                    {
                        if (!Reader[((Column)attr).Name.ToString()].Equals(System.DBNull.Value))
                        {
                            if (Info.PropertyType.GetCustomAttributes(typeof(Table), true).Length > 0)
                            {
                                object fk = NormalFKProxy.CreateProxy(Activator.CreateInstance(Info.PropertyType),
                                    Reader[((Column)attr).Name.ToString()], _connectionString, _providerName);
                                tipe.InvokeMember(Info.Name, BindingFlags.SetProperty, null,
                                ReturnObject, new object[] { fk });
                                Filled = true;
                            }
                            else
                            {
                                tipe.InvokeMember(Info.Name, BindingFlags.SetProperty, null,
                                    ReturnObject, new object[] { Reader[((Column)attr).Name.ToString()] });
                                Filled = true;
                            }
                        }
                    }
                }
            }
            return Filled;
        }

        protected bool ContainsColumn(DbDataReader reader, string columnName)
        {
            for (int x = 0; x < reader.FieldCount; x++)
            {
                if (reader.GetName(x) == columnName)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Recovers the number of records of the generic table
        /// </summary>
        /// <returns>The Number of Records</returns>
        protected long NumberOfRecords()
        {
            return NumberOfRecords((T)null);
        }

        /// <summary>
        /// Recovers the number of records of the generic table
        /// </summary>
        /// <param name="Criteria">The Criteria to count on</param>
        /// <returns>The Number of Records</returns>
        protected long NumberOfRecords(List<string> Criteria)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendLine("Select Count(*)");
            sbSql.AppendLine(" From " + GetTableName(typeof(T)));
            if (Criteria != null && Criteria.Count > 0)
            {
                sbSql.AppendLine(" Where " + ReplaceBySqlDefinition(typeof(T), Criteria[0]));
                for (int x = 1; x < Criteria.Count; x++)
                {
                    sbSql.AppendLine(" And " + ReplaceBySqlDefinition(typeof(T), Criteria[x]));
                }
            }
            DbCommand cmdFind = _connection.CreateCommand();
            cmdFind.CommandType = CommandType.Text;
            cmdFind.CommandText = sbSql.ToString();
            if (_connection.State != ConnectionState.Open)
            {
                Connect(_connection);
            }
            try
            {
                long _return = Convert.ToInt64(cmdFind.ExecuteScalar());
                return _return;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                {
                    Disconnect(_connection);
                }
            }
        }

        /// <summary>
        /// Recovers the number of records of the generic table
        /// </summary>
        /// <param name="Example">The example to use on search</param>
        /// <returns>The Number of Records</returns>
        protected long NumberOfRecords(T Example)
        {
            return NumberOfRecords(CreateCriteria(Example));
        }

        /// <summary>
        /// Create the Criteria based on Example
        /// </summary>
        /// <param name="Example">The Generic type for example</param>
        /// <returns></returns>
        protected List<string> CreateCriteria(T Example)
        {
            List<string> _return = new List<string>();
            if (Example == null)
            {
                return _return;
            }
            foreach (PropertyInfo Info in typeof(T).GetProperties())
            {
                object _value = Example.GetType().InvokeMember(Info.Name, BindingFlags.GetProperty, null, Example, null);
                if (_value != null)
                {
                    foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                    {
                        _return.Add(((Id)attr).Name + " = " + _value);
                    }
                    foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                    {
                        _return.Add(((Column)attr).Name + " = " + _value);
                    }
                }
            }
            return _return;
        }

        /// <summary>
        /// Search for Table Attribute in the Generic class, returning the Table Name
        /// </summary>
        /// <returns></returns>
        protected string GetTableName(Type Clazz)
        {
            string _return = "";
            foreach (object _tblAttr in Clazz.GetCustomAttributes(typeof(Table), true))
            {
                if (((Table)_tblAttr).TableName == null || ((Table)_tblAttr).TableName.Equals(""))
                {
                    _return = Clazz.Name;
                }
                else
                {
                    _return = ((Table)_tblAttr).TableName;
                }
                break;
            }
            return _return;
        }


        /// <summary>
        /// Retrieves a list of parameter values based on given object
        /// </summary>
        /// <param name="Clazz">The object type that will be inferred</param>
        /// <param name="sbSql">The existing StringBuilder that will be added by fields</param>
        protected void GetSqlInsert(Type Clazz, StringBuilder sbSql)
        {
            bool passou = false;
            AutoNumber _anumber = GetAutoNumberAttribute();
            foreach (PropertyInfo Info in Clazz.GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    if (_anumber.Strategy != AutoNumberStrategy.Native)
                    {
                        sbSql.AppendLine("\t" + GetParameterPrefix() + ((Id)attr).Name + ", ");
                        passou = true;
                    }
                }
                foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                {
                    sbSql.AppendLine("\t" + GetParameterPrefix() + ((Column)attr).Name + ", ");
                    passou = true;
                }
            }
            if (passou)
            {
                sbSql = sbSql.Remove(sbSql.Length - 4, 4);
            }
        }


        /// <summary>
        /// Retrieves a list of parameter values based on given object
        /// </summary>
        /// <param name="Clazz">The object type that will be inferred</param>
        /// <param name="sbSql">The existing StringBuilder that will be added by fields</param>
        protected void GetSqlUpdate(Type Clazz, StringBuilder sbSql)
        {
            bool passou = false;
            foreach (PropertyInfo Info in Clazz.GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                {
                    sbSql.Append("\t" + ((Column)attr).Name + " = ");
                    sbSql.AppendLine("\t" + GetParameterPrefix() + ((Column)attr).Name + ", ");
                    passou = true;
                }
            }
            if (passou)
            {
                sbSql = sbSql.Remove(sbSql.Length - 4, 4);
            }
        }

        /// <summary>
        /// Fill the parameter list of for a command
        /// </summary>
        /// <param name="cmdReferred"></param>
        /// <param name="ObjectReferred"></param>
        /// <param name="DoNotIncludeId"></param>
        protected void FillParameters(DbCommand cmdReferred, T ObjectReferred, bool DoNotIncludeId)
        {
            AutoNumber _anumber = GetAutoNumberAttribute();
            foreach (PropertyInfo Info in typeof(T).GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    if (!DoNotIncludeId)
                    {
                        DbParameter _par = _factory.CreateParameter();
                        _par.ParameterName = GetParameterPrefix() + ((Id)attr).Name;
                        _par.Value = typeof(T).InvokeMember(Info.Name,
                            BindingFlags.GetProperty, null, ObjectReferred, null);
                        cmdReferred.Parameters.Add(_par);
                    }
                }
                foreach (object attr in Info.GetCustomAttributes(typeof(Column), true))
                {
                    DbParameter _par = _factory.CreateParameter();
                    _par.ParameterName = GetParameterPrefix() + ((Column)attr).Name;
                    if (Info.PropertyType.GetCustomAttributes(typeof(Table), true).Length > 0)
                    {
                        object objFK = typeof(T).InvokeMember(Info.Name,
                            BindingFlags.GetProperty, null, ObjectReferred, null);
                        if (objFK != null)
                        {
                            foreach (PropertyInfo infFK in objFK.GetType().GetProperties())
                            {
                                foreach (object attrFK in infFK.GetCustomAttributes(typeof(Id), true))
                                {
                                    _par.Value = objFK.GetType().InvokeMember(infFK.Name, BindingFlags.GetProperty, null, objFK, null);
                                }
                            }
                        }
                    }
                    else
                    {
                        _par.Value = typeof(T).InvokeMember(Info.Name,
                            BindingFlags.GetProperty, null, ObjectReferred, null);
                    }
                    if (_par.Value == null)
                    {
                        if (((Column)attr).Required)
                        {
                            string msg = ((Column)attr).MsgRequired;
                            if (msg == null || msg.Equals(""))
                            {
                                msg = "The value of property " + Info.Name + " must be provided";
                            }
                            throw new ArgumentException(msg);
                        }
                        _par.Value = DBNull.Value;
                    }
                    cmdReferred.Parameters.Add(_par);
                }
            }
        }

        /// <summary>
        /// Get The PK as an array of objects
        /// </summary>
        /// <param name="ReferredObject"></param>
        /// <returns></returns>
        protected object[] GetPk(T ReferredObject)
        {
            List<object> lst = new List<object>();
            foreach (PropertyInfo Info in typeof(T).GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(Id), true))
                {
                    lst.Add(typeof(T).InvokeMember(Info.Name,
                        BindingFlags.GetProperty, null, ReferredObject, null));
                }
            }
            return lst.ToArray();
        }

        /// <summary>
        /// Returns the AutoNumberAttribute if any
        /// </summary>
        /// <returns></returns>
        protected AutoNumber GetAutoNumberAttribute()
        {
            AutoNumber _return = null;
            foreach (PropertyInfo Info in typeof(T).GetProperties())
            {
                foreach (object attr in Info.GetCustomAttributes(typeof(AutoNumber), true))
                {
                    _return = (AutoNumber)attr;
                }
            }
            return _return;
        }

        /// <summary>
        /// Returns the command to recover the native auto-numbered id
        /// </summary>
        /// <returns></returns>
        protected string GetAutoNumberQuery()
        {
            string _return = "";
            AutoNumber _anumber = GetAutoNumberAttribute();
            if (_anumber == null || (_anumber.Strategy != AutoNumberStrategy.Native && _anumber.Strategy != AutoNumberStrategy.Sequence))
            {
                return _return;
            }
            if (_anumber.Strategy == AutoNumberStrategy.Native)
            {
                switch (_databaseType)
                {
                    case DatabaseType.MsSQL:
                        _return = "Select Ident_current('" + GetTableName(typeof(T)) + "')";
                        break;
                    case DatabaseType.Oracle:
                        _return = "";
                        break;
                    case DatabaseType.MySQL:
                        _return = "Select last_insert_id()";
                        break;
                    case DatabaseType.Firebird:
                        _return = "Select Gen_id(GEN_" + GetTableName(typeof(T)) + "_ID,0) From RDB$DATABASE;";
                        break;
                }
            }
            if (_anumber.Strategy == AutoNumberStrategy.Sequence)
            {
                _return = "Select " + _anumber.SequenceId +
                    " From " + _anumber.SequenceTableName +
                    " Where " + _anumber.SequenceDescription + " = '" + GetTableName(typeof(T)) + "'";
            }
            return _return;
        }

        /// <summary>
        /// Checks for Open Connection and connects if closed
        /// </summary>
        /// <param name="conn">The connneciton to check/open</param>
        protected void Connect(DbConnection conn)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        }

        /// <summary>
        /// Checks for Open Connection and disconnects if opened
        /// </summary>
        /// <param name="conn">The connection to check/close</param>
        protected void Disconnect(DbConnection conn)
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Gets the parameter prefix based on database type
        /// </summary>
        /// <returns>The parameter prefix (such as "@" for MSSQL and "?" for MYSQL</returns>
        protected string GetParameterPrefix()
        {
            string _return = "";
            switch (_databaseType)
            {
                case DatabaseType.MsSQL:
                    _return = "@";
                    break;
                case DatabaseType.Oracle:
                    _return = "@";
                    break;
                case DatabaseType.MySQL:
                    _return = "?";
                    break;
                case DatabaseType.Firebird:
                    _return = "@";
                    break;
            }
            return _return;
        }

        /// <summary>
        /// Performs an arbitrary command (including stored-procedures and views) and 
        /// returns the first occurrence of typed object, returning null in case of 
        /// non-matching results.
        /// Note that return fields must match with Column Attributes
        /// </summary>
        /// <param name="cmd">The comman to execute</param>
        /// <returns></returns>
        public T ExecuteCommand(DbCommand cmd)
        {
            T ReturnValue = null;
            DbDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                T ReturnObject = (T)NormalProxy.CreateProxy(Activator.CreateInstance(typeof(T)),
                    _connectionString, _providerName);
                if (!FillObject(ReturnValue, reader, typeof(T)))
                {
                    ReturnValue = null;
                }
            }
            return ReturnValue;
        }

        /// <summary>
        /// Performs an arbitrary command (including stored-procedures and views) and 
        /// returns the list of typed object, returning a empty list in case of 
        /// non-matching results
        /// Note that return fields must match with Column Attributes
        /// Be careful with long items results
        /// </summary>
        /// <param name="cmd">The comman to execute</param>
        /// <returns></returns>
        public List<T> ExecuteCommandList(DbCommand cmd)
        {
            List<T> ReturnValue = new List<T>();
            DbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                T Item = (T)NormalProxy.CreateProxy(Activator.CreateInstance(typeof(T)),
                    _connectionString, _providerName);
                if (FillObject(Item, reader, typeof(T)))
                {
                    ReturnValue.Add(Item);
                }
            }
            return ReturnValue;
        }
    }
}
